package cn.tedu.web.ycw;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author 余崇伟
 * @email 1144692398@qq.com
 * @time 2021-12-09 09:13:46
 **/
public class JdbcUtils {
    // 配置文件
    private static final String conf = "/cn/tedu/web/ycw/db.properties";
    // 连接数据库的地址
    private static final String url;
    // 连接数据库的账号
    private static final String username;
    // 连接数据库的密码
    private static final String password;
    // 数据库连接
    private static Connection connection = null;

    // 静态初始化
    static {
        Properties properties = new Properties();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            properties.load(JdbcUtils.class.getResourceAsStream(conf));
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
        // 尝试从配置文件中读取数据库的连接信息
        url = properties.getProperty("url", "jdbc:mysql://localhost:3306/test?useSSL=false");
        username = properties.getProperty("username", "root");
        password = properties.getProperty("password", "ycw");
    }

    /**
     * 获取数据库连接
     */
    private static synchronized Connection getConnection() {
        try {
            // 如果没有连接数据库，或数据库连接被关闭，尝试连接
            if (connection == null || connection.isClosed()) {
                connection = DriverManager.getConnection(url, username, password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 读取结果集并转换为指定的对象
     *
     * @param set 结果集
     * @param c   泛型类对象
     * @param <T> 泛型类
     * @return 返回一组类的实例
     * @throws SQLException, InstantiationException, IllegalAccessException
     */
    @SuppressWarnings("unchecked")
    private static <T> List<T> readSet(ResultSet set, Class<T> c) throws SQLException, InstantiationException, IllegalAccessException {
        // 通过结果集的 getMetaData 可以获取结果集的描述性信息
        ResultSetMetaData data = set.getMetaData();
        // 借助 ResultSetMetaData 得到所有需要赋值的字段的集合
        List<Field> fields = new ArrayList<>();
        // 1. 得到列的总数
        int count = 0;
        try {
            count = data.getColumnCount();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 2. 通过循环，得到每一列的名称和类型
        for (int i = 1; i <= count; i++) {
            // 3. 通过每一列的名称反射字段
            try {
                // 从描述信息中获取列名
                String name = data.getColumnName(i);
                // 从描述信息中获取列的类型
                // int columnType = data.getColumnType(i);
                // String typeName = data.getColumnTypeName(i);
                Field field = c.getDeclaredField(name);
                // 4. 将所有的字段组装为一个集合
                fields.add(field);
            } catch (SQLException | NoSuchFieldException | SecurityException e) {
                // e.printStackTrace();
            }
        }

        List<T> ts = new ArrayList<>();

        while (set.next()) {
            T t = null;
            // 处理基础数据
            if (c.isAssignableFrom(Integer.class)
                    || c.isAssignableFrom(Long.class)
                    || c.isAssignableFrom(Double.class)
                    || c.isAssignableFrom(String.class)) {
                t = (T) set.getObject(1);
            } else if (c.isAssignableFrom(Boolean.class)) {
                long v = set.getLong(1);
                Boolean val = v > 0;
                t = (T) val;
            } else {
                // 1. 反射实例
                t = c.newInstance();
                // 2. 为实例的属性赋值
                for (Field field : fields) {
                    field.setAccessible(true);
                    // 通过字段的名称获取其值，并赋值给实例 t
                    field.set(t, set.getObject(field.getName()));
                }
            }
            ts.add(t);
        }
        set.close();
        return ts;
    }

    public static <T> List<T> selects(String sql, Class<T> c, Object... ps) {
        List<T> ts = new ArrayList<>();
        try {
            PreparedStatement statement = getConnection().prepareStatement(sql);
            if (ps != null && ps.length > 0) {
                for (int i = 0; i < ps.length; i++) {
                    statement.setObject(i + 1, ps[i]);
                }
            }
            ResultSet set = statement.executeQuery();
            ts = readSet(set, c);
            // 关闭查询
            statement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ts;
    }

    public static <T> T select(String sql, Class<T> c, Object... ps) {
        List<T> ts = selects(sql, c, ps);
        if (ts != null && !ts.isEmpty()) {
            return ts.get(0);
        }
        return null;
    }

    public static int modify(String sql, Object... ps) {
        int r = 0;
        try {
            PreparedStatement statement = getConnection().prepareStatement(sql);
            if (ps != null && ps.length > 0) {
                for (int i = 0; i < ps.length; i++) {
                    statement.setObject(i + 1, ps[i]);
                }
            }
            r = statement.executeUpdate();
            // 关闭查询
            statement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }
}
